<?php
include('includes/db/sql.functions.php');
ob_start();
$db=DB::$database;
?>
<style>
p.title{
color:#08429D;
font-weight:bold;
text-transform:uppercase;
margin-bottom: -7px;
}

</style>

<p class="title"> Database </p>
<hr style="float:left; color:#ABE107;  background-color:#ABE107;" >
</br>

<?php

/*=====================================DATABASE=================================================*/
$sql="DROP DATABASE IF EXISTS $db";
debug_query($sql, null,false);

$sql="CREATE DATABASE $db DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
debug_query($sql,null, false);


/*=====================================TABLES=================================================*/
$sql = "CREATE TABLE biobit (
  bb_name VARCHAR(20) NOT NULL,
  bb_desc TEXT, 
  bb_link VARCHAR(50),
  PRIMARY KEY (bb_name))";
debug_query($sql);

$sql = "CREATE TABLE hws (
  hws_name VARCHAR(20) NOT NULL,
  bb_name VARCHAR(20) NOT NULL,
  hws_comments TEXT,
  hws_linkBB VARCHAR(50),
  hws_testBy VARCHAR(50) NOT NULL,
  hws_type ENUM('EHW','RHW','SINK') NOT NULL DEFAULT 'EHW',
  PRIMARY KEY (hws_name),
  FOREIGN KEY (bb_name) REFERENCES biobit(bb_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE gen (
  g_name VARCHAR(10) NOT NULL,
  g_species VARCHAR(50) NOT NULL, 
  g_codSus VARCHAR(70) NOT NULL,
  g_linkBB VARCHAR(50),
  g_linkBD VARCHAR(50),
  g_Fasta TEXT NOT NULL,
  PRIMARY KEY (g_name))";
debug_query($sql);

$sql = "CREATE TABLE genHws (
  hws_name VARCHAR(20) NOT NULL,
  g_name VARCHAR(10) NOT NULL,
  PRIMARY KEY (hws_name,g_name),
FOREIGN KEY (hws_name) REFERENCES hws(hws_name) ON DELETE CASCADE,
FOREIGN KEY (g_name) REFERENCES gen(g_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE targProm (
  tp_name VARCHAR(10) NOT NULL,
  tp_species VARCHAR(15) NOT NULL, 
  tp_Fasta TEXT NOT NULL,
  PRIMARY KEY (tp_name))";
debug_query($sql);

/*chekear q hws sea RHW*/
$sql = "CREATE TABLE tpHws (
  hws_name VARCHAR(20) NOT NULL, 
  tp_name VARCHAR(10) NOT NULL,
  PRIMARY KEY (hws_name,tp_name),
FOREIGN KEY (hws_name) REFERENCES hws(hws_name) ON DELETE CASCADE,
FOREIGN KEY (tp_name) REFERENCES targProm(tp_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE logicCore (
  lc_name VARCHAR(20) NOT NULL,
  lc_desc TEXT,
  lc_comments TEXT, 
  lc_testBy VARCHAR(50) NOT NULL,
  PRIMARY KEY (lc_name))";
debug_query($sql);

$sql = "CREATE TABLE LCinput (
  lci_id INT NOT NULL AUTO_INCREMENT,
  lc_name VARCHAR(20) NOT NULL,
  lci_input TEXT,
  PRIMARY KEY (lci_id),
  FOREIGN KEY (lc_name) REFERENCES logicCore(lc_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE LCoutput (
  lco_id INT NOT NULL AUTO_INCREMENT,
  lc_name VARCHAR(20) NOT NULL,
  lco_output TEXT,
  PRIMARY KEY (lco_id),
  FOREIGN KEY (lc_name) REFERENCES logicCore(lc_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE logCoreGen (
  lc_name VARCHAR(20) NOT NULL,
  g_name VARCHAR(10) NOT NULL,
  PRIMARY KEY (lc_name,g_name),
FOREIGN KEY (lc_name) REFERENCES logicCore(lc_name) ON DELETE CASCADE,
FOREIGN KEY (g_name) REFERENCES gen(g_name) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE wireInstance (
  w_id INT NOT NULL AUTO_INCREMENT,
  bb_name VARCHAR(20) NOT NULL,
  ews_name VARCHAR(20) NOT NULL,
  rws_name VARCHAR(20) NOT NULL,
  sink_name VARCHAR(20) NOT NULL,
  PRIMARY KEY (w_id),
FOREIGN KEY (bb_name) REFERENCES biobit(bb_name) ON DELETE CASCADE,
FOREIGN KEY (ews_name) REFERENCES hws(hws_name) ON DELETE CASCADE,
FOREIGN KEY (rws_name) REFERENCES hws(hws_name) ON DELETE CASCADE,
FOREIGN KEY (sink_name) REFERENCES hws(hws_name) ON DELETE CASCADE)";
debug_query($sql);
//Ver si se puede restringir que sean de un tipo específico cada hws.

$sql = "CREATE TABLE module (
  mod_name VARCHAR(20) NOT NULL,
  mod_desc TEXT, 
  mod_testBy VARCHAR(50) NOT NULL,
  mod_comments TEXT, 
  PRIMARY KEY (mod_name))";
debug_query($sql);

$sql = "CREATE TABLE wireLC (
  mod_name VARCHAR(20) NOT NULL,
  lco_id INT NOT NULL,
  w_id INT NOT NULL,
  lci_id INT NOT NULL,
  PRIMARY KEY (mod_name,lco_id,w_id,lci_id),
  FOREIGN KEY (lco_id) REFERENCES LCoutput(lco_id) ON DELETE CASCADE,
  FOREIGN KEY (lci_id) REFERENCES LCinput(lci_id) ON DELETE CASCADE,
  FOREIGN KEY (w_id) REFERENCES wireInstance(w_id) ON DELETE CASCADE)";
debug_query($sql);

$sql = "CREATE TABLE translator (
  trans_name VARCHAR(20) NOT NULL,
  ews_name VARCHAR(20) NOT NULL,
  rws_name VARCHAR(20) NOT NULL,
  PRIMARY KEY (trans_name),
  FOREIGN KEY (ews_name) REFERENCES hws(hws_name) ON DELETE CASCADE,
  FOREIGN KEY (rws_name) REFERENCES hws(hws_name) ON DELETE CASCADE)";
debug_query($sql);
//Chequear que sean distinto biobit

$sql = "CREATE TABLE transductor (
  transd_name VARCHAR(20) NOT NULL,
  hws_name VARCHAR(20) NOT NULL,
  transd_other VARCHAR(20) NOT NULL,
  transd_type ENUM('input','output') NOT NULL DEFAULT 'input',
  PRIMARY KEY (transd_name),
  FOREIGN KEY (hws_name) REFERENCES hws(hws_name) ON DELETE CASCADE)";
debug_query($sql);
//Chequear que sean distinto biobit

$sql = "CREATE TABLE puser (
  nickname CHAR(15) NOT NULL,
  name_puser CHAR(15) NOT NULL, 
  surname_puser CHAR(30) NOT NULL,
  editor BOOL DEFAULT False,
  email CHAR(50) NOT NULL,
  PRIMARY KEY (nickname))";
debug_query($sql);



echo "</br><a href='demo.php'>Ejecutar datos demo</a></br>";

$content= ob_get_contents();  
ob_end_clean();
include_once('template.php');
?>
